# coding: utf-8
from sqlalchemy import Column, DateTime, Enum, ForeignKey, String, Table, Text,create_engine
from sqlalchemy.dialects.mysql import INTEGER
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

#连接mysql数据库，echo为是否打印结果
engine = create_engine("mysql+pymysql://root:123456@10.0.5.208:3306/ihome_python04",encoding="utf-8",echo=True,max_overflow=5)

Base = declarative_base()
metadata = Base.metadata

'''
from sqlalchemy import create_engine,text
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/library?charset=utf8',echo=False) # pool_size=20, max_overflow=5,echo=True, echo_pool=True
Session = sessionmaker(bind=engine)
session = Session()

pool_size: 连接池大小；
pool_recycle:连接回收时间，这个值必须要比数据库自身配置的interactive_timeout值小；
pool_pre_ping:预检测池中连接是否有效，并替换无效连接；
pool_use_lifo：使用后进先出的方式获取连接，允许多余连接保持空闲；
echo_pool：会打印输出连接池的异常信息，帮助排查问题；
max_overflow： 最大允许溢出连接池大小的连接数量。

注意：
注意按需配置engine参数，sqlalchemy并不能随时知道连接池中的连接是否仍然有限，在应用程序中使用完一个连接后，记得调用close（）方法将连接放回连接池。
一定注意设置连接回收时间小于数据库配置的失效时间。
'''

class IhAreaInfo(Base):
    __tablename__ = 'ih_area_info'

    create_time = Column(DateTime)
    update_time = Column(DateTime)
    id = Column(INTEGER(11), primary_key=True)
    name = Column(String(32), nullable=False)
    houses = relationship('IhHouseInfo',backref="area")


class IhFacilityInfo(Base):
    __tablename__ = 'ih_facility_info'

    create_time = Column(DateTime)
    update_time = Column(DateTime)
    id = Column(INTEGER(11), primary_key=True)
    name = Column(String(32), nullable=False)

    houses = relationship('IhHouseInfo', secondary='ih_house_facility')


class IhUserProfile(Base):
    __tablename__ = 'ih_user_profile'

    create_time = Column(DateTime)
    update_time = Column(DateTime)
    id = Column(INTEGER(11), primary_key=True)
    name = Column(String(32), nullable=False, unique=True)
    password_hash = Column(String(128), nullable=False)
    mobile = Column(String(11), nullable=False, unique=True)
    real_name = Column(String(32))
    id_card = Column(String(20))
    avatar_url = Column(String(128))
    houses = relationship("IhHouseInfo", backref="user")  # 用户发布的房屋
    orders = relationship("ih_order_info", backref="user")  # 用户下的订单


class IhHouseInfo(Base):
    __tablename__ = 'ih_house_info'

    create_time = Column(DateTime)
    update_time = Column(DateTime)
    id = Column(INTEGER(11), primary_key=True)
    user_id = Column(ForeignKey('ih_user_profile.id'), nullable=False, index=True)
    area_id = Column(ForeignKey('ih_area_info.id'), nullable=False, index=True)
    title = Column(String(64), nullable=False)
    price = Column(INTEGER(11))
    address = Column(String(512))
    room_count = Column(INTEGER(11))
    acreage = Column(INTEGER(11))
    unit = Column(String(32))
    capacity = Column(INTEGER(11))
    beds = Column(String(64))
    deposit = Column(INTEGER(11))
    min_days = Column(INTEGER(11))
    max_days = Column(INTEGER(11))
    order_count = Column(INTEGER(11))
    index_image_url = Column(String(256))

    area = relationship('IhAreaInfo')
    user = relationship('IhUserProfile')


t_ih_house_facility = Table(
    'ih_house_facility', metadata,
    Column('house_id', ForeignKey('ih_house_info.id'), primary_key=True, nullable=False),
    Column('facility_id', ForeignKey('ih_facility_info.id'), primary_key=True, nullable=False, index=True)
)


class IhHouseImage(Base):
    __tablename__ = 'ih_house_image'

    create_time = Column(DateTime)
    update_time = Column(DateTime)
    id = Column(INTEGER(11), primary_key=True)
    house_id = Column(ForeignKey('ih_house_info.id'), nullable=False, index=True)
    url = Column(String(256), nullable=False)

    house = relationship('IhHouseInfo')


class IhOrderInfo(Base):
    __tablename__ = 'ih_order_info'

    create_time = Column(DateTime)
    update_time = Column(DateTime)
    id = Column(INTEGER(11), primary_key=True)
    user_id = Column(ForeignKey('ih_user_profile.id'), nullable=False, index=True)
    house_id = Column(ForeignKey('ih_house_info.id'), nullable=False, index=True)
    begin_date = Column(DateTime, nullable=False)
    end_date = Column(DateTime, nullable=False)
    days = Column(INTEGER(11), nullable=False)
    house_price = Column(INTEGER(11), nullable=False)
    amount = Column(INTEGER(11), nullable=False)
    status = Column(Enum('WAIT_ACCEPT', 'WAIT_PAYMENT', 'PAID', 'WAIT_COMMENT', 'COMPLETE', 'CANCELED', 'REJECTED'), index=True)
    comment = Column(Text)

    house = relationship('IhHouseInfo')
    user = relationship('IhUserProfile')


metadata.create_all(engine) #创建表结构